
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;

@ManagedBean
@RequestScoped
public class ContatoMB {

    private Contato contato;
    private Connection con;
    private PreparedStatement pstm;
    private List<Contato> contatos;
    private int idInicial;
    private int idFinal;
    private Date dataInicial;
    private Date dataFinal;

    public ContatoMB() throws SQLException {
        contato = new Contato();
        DriverManager.registerDriver(new org.postgresql.Driver());
        con = DriverManager.getConnection(
                "jdbc:postgresql://localhost/agenda",
                "postgres",
                "postgres");
        this.listaContatos("select * from contatos");
    }

    public void setContato(Contato contato) {
        this.contato = contato;
    }

    public Contato getContato() {
        return this.contato;
    }

    public List<Contato> getContatos() {
        return this.contatos;
    }

    public void setContatos(List<Contato> contatos) {
        this.contatos = contatos;
    }    

    public int getIdInicial() {
        return idInicial;
    }

    public void setIdInicial(int idInicial) {
        this.idInicial = idInicial;
    }

    public int getIdFinal() {
        return idFinal;
    }

    public void setIdFinal(int idFinal) {
        this.idFinal = idFinal;
    }   

    public Date getDataInicial() {
        return dataInicial;
    }

    public void setDataInicial(Date dataInicial) {
        this.dataInicial = dataInicial;
    }

    public Date getDataFinal() {
        return dataFinal;
    }

    public void setDataFinal(Date dataFinal) {
        this.dataFinal = dataFinal;
    }       

    public void novo(){
        contato = new Contato();            
    }
    
    public void gravar() throws SQLException {
        if(contato.getId() > 0)
            alterar(contato);                         
        else if(contato.getId() == 0)
            salvar();                         
    }
       
    public void salvar() throws SQLException {
        pstm = con.prepareStatement(
                "insert into contatos(id, nome, email, nascimento) "
                + "values (nextval('seq_contatos'), ?, ?, ?)");
        pstm.setString(1, contato.getNome());
        pstm.setString(2, contato.getEmail());
        pstm.setDate(3, new java.sql.Date(contato.getNascimento().getTime()));
        pstm.executeUpdate();
        pstm.close();
        this.listaContatos("select * from contatos");
    }
    
    public void pesquisar() throws SQLException{
        pstm = con.prepareStatement("select * from contatos where id = ?");
        pstm.setInt(1, contato.getId());
        ResultSet rs;
        rs = pstm.executeQuery();
        if(rs.next()){
            contato.setNome(rs.getString("nome"));
            contato.setEmail(rs.getString("email"));
            contato.setNascimento(rs.getDate("nascimento"));
        }
        con.close();
    }
    
    public void editar(Object o) throws SQLException{
        this.setContato((Contato) o);
    }
    
    public void alterar(Object o) throws SQLException{
        Contato contato = (Contato) o;
        pstm = con.prepareStatement(
                "update contatos set nome = ?, email = ?, nascimento = ? where id = ?");
        pstm.setString(1, contato.getNome());
        pstm.setString(2, contato.getEmail());
        pstm.setDate(3, new java.sql.Date(contato.getNascimento().getTime()));
        pstm.setInt(4, contato.getId());
        pstm.executeUpdate();
        pstm.close();
        this.listaContatos("select * from contatos");
    }
    
    public void excluir(Object o) throws SQLException{
        Contato contato = (Contato) o;
        pstm = con.prepareStatement("delete from contatos where id = ?");
        pstm.setInt(1, contato.getId());
        pstm.executeUpdate();
        pstm.close();
        this.novo();        
        this.listaContatos("select * from contatos");
    }
    
    public void listaContatos(String sql) throws SQLException{
        contatos = new ArrayList<Contato>();
        pstm = con.prepareStatement(sql);
        ResultSet rs;
        rs = pstm.executeQuery();
        while(rs.next()){
            contatos.add(new Contato(rs.getInt("id"), 
                    rs.getString("nome"), 
                    rs.getString("email"),
                    rs.getDate("nascimento")));
        }
        pstm.close();   
        this.contatos = contatos;        
    }
    
    public void ordenaID() throws SQLException{
        this.listaContatos("select * from contatos order by id");
    }
    
    public void ordenaNomeASC() throws SQLException{
        this.listaContatos("select * from contatos order by nome");
    }
    
    public void ordenaNomeDESC() throws SQLException{
        this.listaContatos("select * from contatos order by nome DESC");
    }
    
    public void ordenaEmail() throws SQLException{
        this.listaContatos("select * from contatos order by email");
    }
    
    public void filtraNascimento() throws SQLException{
        this.listaContatos("select * from contatos where nascimento between '" + dataInicial + "' and '" + dataFinal + "'");
    }
    
    public void filtraID() throws SQLException{
        this.listaContatos("select * from contatos where id >= " + idInicial + " and id <= " + idFinal);
    }
    
    public void listaTodos() throws SQLException{
        this.listaContatos("select * from contatos");
        dataInicial = null;
        dataFinal = null;
        idInicial = 0;
        idFinal = 0;
    }
       
}
